package com.xuan.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import com.xuan.model.Game;
import com.xuan.model.Section;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
public class SectionDao extends BaseDao {
	public boolean add(Section section) {
		String sql = "insert into accompanying_play" +
				"  (id, name, level_, order_count, loc, acc_img, message, price, dan, game_id)" +
				" values" +
				"  ((select max(id) from accompanying_play)+1, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			return queryRunner.execute(sql,
					section.getName(),
					section.getLevel_(),
					section.getOrder_count(),
					section.getLoc(),
					section.getAcc_img(),
					section.getMessage(),
					section.getPrice(),
					section.getDan(),
					section.getGame_id().getId()) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 *
	 * */
	public boolean delete(int id) {
		String sql = "delete accompanying_play" +
				" where id = ?";
		try {
			return queryRunner.execute(sql,id) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean update(Section section) {
		String sql = "update accompanying_play set" +
				"  name = ?, level_ = ?, order_count = ?," +
				" loc = ?, acc_img = ?, message = ?," +
				" price = ?, dan = ?, game_id = ? where id = ?";
		Section section1 = get(section.getId());
		if (section.getAcc_img() == null && section1.getAcc_img() != null) {
			section.setAcc_img(section1.getAcc_img());
		}
		try {
			return queryRunner.execute(sql,
					section.getName(),
					section.getLevel_(),
					section.getOrder_count(),
					section.getLoc(),
					section.getAcc_img(),
					section.getMessage(),
					section.getPrice(),
					section.getDan(),
					section.getGame_id().getId(),
					section.getId()) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<Section> getAll() {
		try {
			List<Section> list = new LinkedList<>();
			String sql = "select ap.id aid, ap.name aname, level_, "
					+ " order_count, loc, acc_img, message, price, "
					+ " dan, game_id,g.id gid,g.name "
					+ "from accompanying_play ap" +
					" join game g on game_id = g.id"
					+ " order by level_ desc,"
					+ "order_count desc";
			return queryRunner.query(sql, new ResultSetHandler<List<Section>>() {
				@Override
				public List<Section> handle(ResultSet rs) throws SQLException {
					while (rs.next()) {
						list.add(new Section(rs.getInt("aid"),
								rs.getString("aname"),
								rs.getDouble("level_"),
								rs.getInt("order_count"),
								rs.getString("loc"),
								rs.getString("acc_img"),
								rs.getString("message"),
								rs.getInt("price"),
								rs.getString("dan"),
								new Game(rs.getInt("game_id"),
										rs.getString("name"))));

					}
					return list;
				}

			});
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return null;
	}

	public List<Section> getAll(int id) {
		try {
			List<Section> list = new LinkedList<>();
			String sql = "select ap.id aid, ap.name aname, level_, "
					+ " order_count, loc, acc_img, message, price, "
					+ " dan, game_id,g.id gid,g.name "
					+ "from accompanying_play ap" +
					" join game g on game_id = g.id where g.id = ?";
			return queryRunner.query(sql, new ResultSetHandler<List<Section>>() {
				@Override
				public List<Section> handle(ResultSet rs) throws SQLException {
					while (rs.next()) {
						list.add(new Section(rs.getInt("aid"),
								rs.getString("aname"),
								rs.getDouble("level_"),
								rs.getInt("order_count"),
								rs.getString("loc"),
								rs.getString("acc_img"),
								rs.getString("message"),
								rs.getInt("price"),
								rs.getString("dan"),
								new Game(rs.getInt("game_id"),
										rs.getString("name"))));

					}
					return list;
				}

			}, id);
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return null;
	}

	public Section get(int id) {
		String sql = "select ap.id aid, ap.name aname, level_, "
				+ " order_count, loc, acc_img, message, price, "
				+ " dan, game_id,g.id gid,g.name "
				+ "from accompanying_play ap" +
				" join game g on game_id = g.id where ap.id = ?";
		List<Section> list = new LinkedList<>();

		try {
			return queryRunner.query(sql, new ResultSetHandler<Section>() {
				@Override
				public Section handle(ResultSet rs) throws SQLException {
					Section section = null;
					if (rs.next()) {
						section = new Section(rs.getInt("aid"),
								rs.getString("aname"),
								rs.getDouble("level_"),
								rs.getInt("order_count"),
								rs.getString("loc"),
								rs.getString("acc_img"),
								rs.getString("message"),
								rs.getInt("price"),
								rs.getString("dan"),
								new Game(rs.getInt("game_id"),
										rs.getString("name")));
					}
					return section;
				}
			}, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Section> getAll(int size, int page) {
		List<Section> list = new LinkedList<>();
		String sql = "select ap.id aid, ap.name aname, level_, "
				+ " order_count, loc, acc_img, message, price, "
				+ " dan, game_id,g.id gid,g.name "
				+ "from accompanying_play ap" +
				" join game g on game_id = g.id"
				+ " where ap.id not in( select id from accompanying_play where rownum <= ? )" +
				" and rownum <= ? order by aid";
		try {
			return queryRunner.query(sql, new ResultSetHandler<List<Section>>() {
				@Override
				public List<Section> handle(ResultSet rs) throws SQLException {
					while (rs.next()) {
						list.add(new Section(rs.getInt("aid"),
								rs.getString("aname"),
								rs.getDouble("level_"),
								rs.getInt("order_count"),
								rs.getString("loc"),
								rs.getString("acc_img"),
								rs.getString("message"),
								rs.getInt("price"),
								rs.getString("dan"),
								new Game(rs.getInt("game_id"),
										rs.getString("name"))));

					}
					return list;
				}
			}, (page - 1) * size, size);
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return null;
	}

	public int getPageSize(int size) {
		String sql = "select count(1) from accompanying_play";
		try {
			double res = Double.valueOf(queryRunner.query(sql, new ScalarHandler<>()).toString());
			return (int) Math.ceil(res / size);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
}